package com.class1group3.demo.mapper;

import com.class1group3.demo.pojo.Books;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface BooksMapper {
    @Select("select * from books where id = #{id}")
    Books getBooksById(Integer id);
    @Select("SELECT * FROM books WHERE id = #{id}")
    Map<String, Object> getBooksByIdAsMap(Integer id);
//    @Select("SELECT \n" +
//            "    rating_level AS rating,          -- 评分等级（5星到1星）\n" +
//            "    SUM(rating_count) AS total_ratings, -- 该等级总评分数\n" +
//            "    ROUND(SUM(rating_count) * 100.0 / NULLIF(SUM(SUM(rating_count)) OVER (), 0), 2) AS percentage -- 占比（保留两位小数）\n" +
//            "FROM (\n" +
//            "    -- 拆分每个评分等级的数量\n" +
//            "    SELECT \n" +
//            "        5 AS rating_level, \n" +
//            "        CAST(SPLIT(rating_dist5, ':')[1] AS INT) AS rating_count \n" +
//            "    FROM books\n" +
//            "    UNION ALL\n" +
//            "    SELECT \n" +
//            "        4 AS rating_level, \n" +
//            "        CAST(SPLIT(rating_dist4, ':')[1] AS INT) AS rating_count \n" +
//            "    FROM books\n" +
//            "    UNION ALL\n" +
//            "    SELECT \n" +
//            "        3 AS rating_level, \n" +
//            "        CAST(SPLIT(rating_dist3, ':')[1] AS INT) AS rating_count \n" +
//            "    FROM books\n" +
//            "    UNION ALL\n" +
//            "    SELECT \n" +
//            "        2 AS rating_level, \n" +
//            "        CAST(SPLIT(rating_dist2, ':')[1] AS INT) AS rating_count \n" +
//            "    FROM books\n" +
//            "    UNION ALL\n" +
//            "    SELECT \n" +
//            "        1 AS rating_level, \n" +
//            "        CAST(SPLIT(rating_dist1, ':')[1] AS INT) AS rating_count \n" +
//            "    FROM books\n" +
//            ") t\n" +
//            "GROUP BY rating_level\n" +
//            "ORDER BY rating_level DESC")
    @Select("select * from book_rating_summary")
    List<Map<String, Object>> groupByRatingDistribution();

//    @Select("SELECT \n" +
//            "            authors,                  \n" +
//            "            AVG(rating) AS avg_rating, \n" +
//            "            COUNT(*) AS book_count   \n" +
//            "        FROM \n" +
//            "            books\n" +
//            "        GROUP BY \n" +
//            "            authors ")
    @Select("SELECT * FROM author_summary")
    List<Map<String, Object>> groupByAuthorsStats();

//    @Select("SELECT\n" +
//            "            publish_year,\n" +
//            "            COUNT(*) AS book_count,\n" +
//            "            CASE \n" +
//            "        WHEN LAG(COUNT(*), 1) OVER (ORDER BY publish_year) IS NULL THEN NULL\n" +
//            "        WHEN LAG(COUNT(*), 1) OVER (ORDER BY publish_year) = 0 THEN NULL\n" +
//            "        ELSE ROUND(\n" +
//            "            (COUNT(*) - LAG(COUNT(*), 1) OVER (ORDER BY publish_year)) * 100.0 / \n" +
//            "            LAG(COUNT(*), 1) OVER (ORDER BY publish_year),\n" +
//            "            2\n" +
//            "        )\n" +
//            "    END AS growth_rate_percent\n" +
//            "        FROM books\n" +
//            "        GROUP BY publish_year\n" +
//            "        ORDER BY publish_year")  //计算出年平均增长率，上年没有默认为0
     @Select("select * from yearly_book_growth")                                         // （如growth_rate_percent=50意味着增长了50%，book_count如2000：50   2001：75）
    List<Map<String, Object>> selectYearlyBookCount();
//    @Select("SELECT publish_year, COUNT(*) as count FROM books GROUP BY publish_year")
//    @MapKey("publish_year")
//    Map<Integer, Integer> countBooksByYear();
    //List< Map<String, Object>> getBooksByOrder(Books books);
    @Select("select * from book_rating_bins")
    List<Map<String, Object>> countBooksByRating();
    @Select("select * from hot_publishers")
    List<Map<String, Object>> countHotPublisher();
}
